from django.shortcuts import render

from django.http import HttpResponse, StreamingHttpResponse
from django.utils import timezone
from django.db import connection
import time
import csv
import json


def index(request):
    return HttpResponse('hello world')


def shuidiview(request):
    cursor = connection.cursor()
    cursor.execute('select time from oppojiankong')
    jtime = cursor.fetchone()[0]

    cursor.execute('select shuidi from oppojiankong')
    shuidi = json.loads(cursor.fetchone()[0])
    print(shuidi)

    return render(request, 'shuidi.html', {'time': jtime, 'shuidi': shuidi['data']['data']})


def gaojingview(request):
    cursor = connection.cursor()
    cursor.execute(
        'SELECT DATE_FORMAT(time, "%Y-%m-%d" ) as time ,COUNT(time) as num FROM shuidi GROUP BY DATE_FORMAT(time, "%Y-%m-%d" )')
    temp = cursor.fetchall()
    date = []
    num = []
    for j in temp:
        date.append(j[0])
        num.append(j[1])

    cursor.execute(
        "select wentimiaoshu,count(*) as leixing from shuidi WHERE time>DATE_SUB(CURDATE(), INTERVAL 60 DAY) group by wentimiaoshu ORDER BY `leixing` DESC")
    gaojinbilie = cursor.fetchall()
    bilie60 = []
    for v in gaojinbilie:
        bilie60.append({'value': v[1], 'name': v[0]})

    cursor.execute(
        "SELECT yw,count(*) num FROM shuidi WHERE EXTRACT(YEAR FROM time)=EXTRACT(YEAR FROM CURDATE()) AND EXTRACT(MONTH FROM time)=EXTRACT(MONTH FROM CURDATE()) GROUP BY yw ORDER BY `num` DESC")
    dangyuepaihang = cursor.fetchall()
    dangyueywname = []
    dangyueywnmber = []
    for j in dangyuepaihang:
        if j[0] == '*':
            continue
        dangyueywname.append(j[0])
        dangyueywnmber.append(j[1])

    cursor.execute(
        "SELECT yw,count(*) num FROM shuidi WHERE time>DATE_SUB(CURDATE(), INTERVAL 90 DAY) GROUP BY yw ORDER BY `num` DESC")
    info = cursor.fetchall()
    ywname = []
    ywnmber = []
    ywtongji90 = ''
    for j in info:
        if j[0] == '*':
            continue
        ywname.append(j[0])
        ywnmber.append(j[1])
        ywtongji90 += str(j[0]) + ':' + str(j[1]) + "<br>"
    cursor.close()

    context = {'lishiriqi': date, 'lishishuzi': num, 'bili60': bilie60, 'dangyueywname': dangyueywname,
               'dangyueywnmber': dangyueywnmber, 'ywm': ywname, 'yws': ywnmber, 'ywtongji90': ywtongji90}

    return render(request, 'index.html', context)


def gaojing(request):
    if request.method == 'POST':
        cursor = connection.cursor()
        time = request.POST.get('t')
        shuidi = request.POST.get('s')
        sql = "UPDATE oppojiankong SET time=%s,shuidi=%s"
        cursor.execute(sql, [time, shuidi])
        return HttpResponse('1')

    if request.method == 'GET':
        z = request.GET.get('z')
        if z == 'time':
            cursor = connection.cursor()
            sql = "select time from oppojiankong"
            cursor.execute(sql)
            v = cursor.fetchone()
            return HttpResponse(v)
        if z == 'shuidi':
            cursor = connection.cursor()
            sql = "select shuidi from oppojiankong"
            cursor.execute(sql)
            v = cursor.fetchone()
            return HttpResponse(v)

    return HttpResponse('0')


def csvs(request):
    cursor = connection.cursor()
    cursor.execute(
        'SELECT * from shuidi')
    temp = cursor.fetchall()

    with open('shuidi/static/test.csv', 'w', encoding='utf-8', newline='')as f:
        csv_writer = csv.writer(f)
        csv_writer.writerow(["告警时间", "主机名", "业务", "业务信息", "问题描述", "告警标题", "日志", "结束时间"])
        for i in temp:
            csv_writer.writerow([i[0], i[1], i[2], i[3], i[4], i[5], i[6], i[7]])
        f.close()

    file = open('shuidi/static/test.csv', 'rb')
    response = StreamingHttpResponse(file)
    response['Content-Type'] = 'application/octet-stream'
    response['Content-Disposition'] = 'attachment;filename="test.csv"'
    return response
